/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package br.com.sales.hanserver.action.hancadpe;

import static br.com.sales.hanserver.action.hancadpe.consts.HanCadPEServerConsts.GET_LAST_PERSON;
import static br.com.sales.hanserver.action.hancadpe.consts.HanCadPEServerConsts.GET_PERSON;
import static br.com.sales.hanserver.action.hancadpe.consts.HanCadPEServerConsts.GET_PERSON_UPDATE;
import static br.com.sales.hanserver.action.hancadpe.consts.HanCadPEServerConsts.VALIDATE;
import static br.com.sales.hanserver.dao.Dao.closeConn;
import static br.com.sales.hanserver.dao.Dao.getConn;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.LinkedHashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author v
 */
public class GetDataPersonGridAction {

    public GetDataPersonGridAction() {
    }

    public List<Map> getDataGrid(int page) {
        List<Map> resultData = new LinkedList<>();
        try {
            PreparedStatement st = getConn().prepareStatement(GET_PERSON);
            st.setInt(1, page);

            ResultSet rs = st.executeQuery();
            while (rs.next()) {
                Map map = new LinkedHashMap(3);
                map.put("SEQ", rs.getInt("SEQ"));
                map.put("NM_PESSOA_FISICA", rs.getString("NM_PESSOA_FISICA"));
                map.put("IE_TIPO_PESSOA", rs.getString("IE_TIPO_PESSOA"));
                map.put("DT_NASCIMENTO", rs.getString("DT_NASCIMENTO"));
                map.put("CD_CPF", rs.getString("CD_CPF"));
                map.put("CD_RG", rs.getString("CD_RG"));
                map.put("DS_ENDERECO", rs.getString("DS_ENDERECO"));
                map.put("NR_TELEFONE", rs.getString("NR_TELEFONE"));
                map.put("NR_CELULAR", rs.getString("NR_CELULAR"));
                map.put("NR_SEQ_PAIS", rs.getString("NR_SEQ_PAIS"));
                map.put("NR_SEQ_ESTADO", rs.getString("NR_SEQ_ESTADO"));
                map.put("NR_SEQ_CIDADE", rs.getString("NR_SEQ_CIDADE"));
                map.put("NM_USUARIO", rs.getString("NM_USUARIO"));
                map.put("DS_SENHA", rs.getString("DS_SENHA"));
                map.put("DS_PAIS", rs.getString("DS_PAIS"));
                map.put("DT_REGISTRO", rs.getString("DT_REGISTRO"));
                map.put("DS_ESTADO", rs.getString("DS_ESTADO"));
                map.put("DS_CIDADE", rs.getString("DS_CIDADE"));
                map.put("IE_TIPO_USUARIO", rs.getString("IE_TIPO_USUARIO"));
                resultData.add(map);
            }

            return resultData;

        } catch (Exception ex) {
            Logger.getLogger(GetDataPersonGridAction.class.getName()).log(Level.SEVERE, "Error getDataGrid", ex);
        } finally {
            closeConn();
        }
        return resultData;
    }

    public Map getLastPerson(Long sequence) {
        Map map = new LinkedHashMap(3);
        try {
            Statement st;
            ResultSet rs;

            if (sequence > 0) {
                st = getConn().prepareCall(GET_PERSON_UPDATE);
                ((PreparedStatement) st).setLong(1, sequence);
                rs = ((PreparedStatement) st).executeQuery();
            } else {
                st = getConn().createStatement();
                rs = st.executeQuery(GET_LAST_PERSON);
            }

            while (rs.next()) {
                map.put("SEQ", rs.getInt("SEQ"));
                map.put("NM_PESSOA_FISICA", rs.getString("NM_PESSOA_FISICA"));
                map.put("IE_TIPO_PESSOA", rs.getString("IE_TIPO_PESSOA"));
                map.put("DT_NASCIMENTO", rs.getString("DT_NASCIMENTO"));
                map.put("CD_CPF", rs.getString("CD_CPF"));
                map.put("CD_RG", rs.getString("CD_RG"));
                map.put("DS_ENDERECO", rs.getString("DS_ENDERECO"));
                map.put("NR_TELEFONE", rs.getString("NR_TELEFONE"));
                map.put("NR_CELULAR", rs.getString("NR_CELULAR"));
                map.put("NR_SEQ_PAIS", rs.getString("NR_SEQ_PAIS"));
                map.put("NR_SEQ_ESTADO", rs.getString("NR_SEQ_ESTADO"));
                map.put("NR_SEQ_CIDADE", rs.getString("NR_SEQ_CIDADE"));
                map.put("NM_USUARIO", rs.getString("NM_USUARIO"));
                map.put("DS_SENHA", rs.getString("DS_SENHA"));
                map.put("DT_REGISTRO", rs.getString("DT_REGISTRO"));
                map.put("DS_PAIS", rs.getString("DS_PAIS"));
                map.put("DS_ESTADO", rs.getString("DS_ESTADO"));
                map.put("DS_CIDADE", rs.getString("DS_CIDADE"));
                map.put("IE_TIPO_USUARIO", rs.getString("IE_TIPO_USUARIO"));
            }

            return map;

        } catch (Exception ex) {
            Logger.getLogger(GetDataPersonGridAction.class.getName()).log(Level.SEVERE, "Error getLastPerson", ex);
        } finally {
            closeConn();
        }
        return map;
    }

    public boolean validate(Map itens) {
        boolean valid = false;
        try {
            PreparedStatement st = getConn().prepareStatement(VALIDATE);
            st.setString(1, (String) itens.get("CPF"));
            st.setString(2, (String) itens.get("RG"));

            ResultSet rs = st.executeQuery();

            while (rs.next()) {
                valid = (rs.getInt("value") == 1);
            }

        } catch (Exception ex) {
            Logger.getLogger(GetDataPersonGridAction.class.getName()).log(Level.SEVERE, "Error validate", ex);
        } finally {
            closeConn();
        }
        return valid;
    }
}
